package com.kfd.daoImpl;

import com.kfd.bean.Eadmin;
import com.kfd.dao.EadminDao;
import com.kfd.exception.DuplicateCodeException;
import com.kfd.exception.DuplicateNumberException;
import com.kfd.util.DateFormatUtil;
import com.kfd.util.JDBCutil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.*;

public class EadminDaoImpl extends JDBCutil implements EadminDao {

    private static final String SQL_UPDATE_LOGIN_ID_TIME = "UPDATE EADMIN SET LOGINIP=?,LOGINTIME=? WHERE USERNAME=?";
    private static final String SQL_SELECT_LOGIN = "SELECT USERPHONE FROM EADMIN WHERE USERNAME=? AND PASSWORD=? AND STATUS=2";
    //    返回控制台需要打印的内容（总用户人数+新增）（快递员人数+新增）
    private static final String SQL_CONSOLE = "SELECT\n" +
            "\tCOUNT( STATUS BETWEEN 0 AND 1\t) data1_size,\n" +
            "\tCOUNT( TO_DAYS( CREATETIME ) = TO_DAYS( NOW( ) ) OR NULL ) data1_day,\n" +
            "\tCOUNT( STATUS = 1 OR NULL ) data2_size,\n" +
            "\tCOUNT( TO_DAYS( CREATETIME ) = TO_DAYS( NOW( ) ) AND STATUS = 1 OR NULL ) data2_day \n" +
            "FROM\n" +
            "\tEADMIN ";
    //查询所有用户信息  根据status判断是用户还是快递员
    private static final String SQL_FIND_ALL_USER = "SELECT * FROM EADMIN WHERE STATUS=?";
    //    用于分页查询快递信息
    private static final String SQL_FIND_LIMIT = "SELECT * FROM EADMIN WHERE STATUS=? LIMIT ?,?";
    //根据手机号查询用户
    private static final String SQL_FIND_PHONE="SELECT * FROM EADMIN WHERE USERPHONE=? AND STATUS=?";
    //增加用户
    private static final String SQL_INSERT="INSERT INTO `e`.`eadmin`(name,`username`, `password`, `loginip`, `logintime`, `createtime`, `userPhone`, `idNO`, `status`) VALUES (?,?, ?, ?, ?, ?, ?, ?, ?)";

    //增加用户
    private static final String SQL_INSERT_SMS="INSERT INTO `e`.`eadmin`(`loginip`, `logintime`, `createtime`, `userPhone`, `status`) VALUES (?,?, ?, ?, ?)";
    //删除用户
    private static final String SQL_DELETE="DELETE FROM EADMIN WHERE ID=? AND STATUS=?";
    //修改用户  ; 有问题，手机号修改需要更改已派件快递手机号也更改
    private static final String SQL_UPDATE="UPDATE EADMIN SET NAME=?, USERNAME=?,PASSWORD=?,USERPHONE=?,IDNO=? WHERE ID=?";
    //根据id查询用户
    private static final String SQL_FIND_BY_ID="SELECT * FROM EADMIN WHERE ID=?";

    /**
     * 根据用户名，更新用户ip，和上次登陆时间
     *
     * @param username 用户名
     * @param date     当前时间
     * @param ip       当前ip
     */
    @Override
    public void updateLoginIpTime(String username, Date date, String ip) {
        List<String> list = new ArrayList<String>(){{
            add(ip);
            add(DateFormatUtil.format(date));
            add(username);
        }};
        try {
            int update = update(SQL_UPDATE_LOGIN_ID_TIME, list);
        } catch (DuplicateNumberException | DuplicateCodeException e) {
            e.printStackTrace();
        }
        close();
    }

    /**
     * 登录方法
     *
     * @param username 用户输入的用户名
     * @param password 用户输入的密码
     * @return 是否登陆成功
     */
    @Override
    public String login(String username, String password) {
        List<String> list = new ArrayList<String>(){{
           add(username);
           add(password);
        }};
        ResultSet select = select(SQL_SELECT_LOGIN, list);
        String userPhone=null;
        try {
            if(select.next()){
                userPhone = select.getString("userPhone");
            }
            return userPhone;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close();
        }
        return userPhone;
    }

    /**
     * 控制台方法
     *
     * @return 返回控制台所需的信息
     */
    @Override
    public List<Map<String, Integer>> console() {
        List<Map<String, Integer>> list = new ArrayList<>();

        ResultSet select = select(SQL_CONSOLE, null);
        try {
            if(select.next()){
                int data1_size = select.getInt("data1_size");
                int data1_day = select.getInt("data1_day");
                int data2_size = select.getInt("data2_size");
                int data2_day = select.getInt("data2_day");
                Map<String,Integer> map1 = new HashMap<String,Integer>();
                map1.put("data1_size",data1_size);
                map1.put("data1_day",data1_day);
                Map<String,Integer> map2 = new HashMap<String,Integer>();
                map2.put("data2_size",data2_size);
                map2.put("data2_day",data2_day);
                list.add(map1);
                list.add(map2);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close();
        }
        if(list.size()==0)
            return null;
        return list;
    }

    /**
     * 查询所有用户
     *
     * @param limit      是否开启分页；true表示开启分页
     * @param offset     sql语句起始下标
     * @param pageNumber 共查询多少条
     * @param status     0，用户页面，1.快递员页面
     * @return 用户的集合
     */
    @Override
    public List<Eadmin> findAll(boolean limit, int offset, int pageNumber, int status) {
        List<Eadmin> list;
        if(limit){
            //为占位符赋值
            list = selectQueryAll(select(SQL_FIND_LIMIT,new ArrayList(){{add(status);add(offset);add(pageNumber);}}), Eadmin.class, null);
        }else{
            list = selectQueryAll(select(SQL_FIND_ALL_USER, new ArrayList(){{add(String.valueOf(status));}}), Eadmin.class, null);
        }
        return list;
    }

    /**
     * 根据手机号查询用户信息
     *
     * @param phone  用户手机号
     * @param status 0,用户；1.快递员
     * @return 查询到的用户信息，有可能为空返回null，手机号不存在
     */
    @Override
    public Eadmin findByPhone(String phone, int status) {
        List list = selectQueryAll(select(SQL_FIND_PHONE, new ArrayList() {{
            add(phone);
            add(String.valueOf(status));
        }}), Eadmin.class, null);
        return list.size() == 0?null:(Eadmin) list.get(0);
    }

    /**
     * 用户的录入
     *
     * @param eadmin 用户信息
     * @param status 0.用户，1.快递员
     * @return 返回是否录入成功
     */
    @Override
    public boolean insert(Eadmin eadmin, int status) throws DuplicateCodeException, DuplicateNumberException {
//        username`, `password`, `loginip`, `logintime`, `createtime`, `userPhone`, `idNO`, `status`
        int update=0;
        if(eadmin.getPassword() != null || !"".equals(eadmin.getPassword())){
            update = update(SQL_INSERT, new ArrayList() {{
                add(eadmin.getName());
                add(eadmin.getUsername());
                add(eadmin.getPassword());
                add(eadmin.getLoginip());
                add(eadmin.getLogintime());
                add(eadmin.getCreatetime());
                add(eadmin.getUserPhone());
                add(eadmin.getIdNO());
                add(status);
            }});
        }else{
                update = update(SQL_INSERT_SMS, new ArrayList() {{
            add(eadmin.getLoginip());
            add(String.valueOf(Timestamp.valueOf(DateFormatUtil.format(new Date()))));
            add(String.valueOf(Timestamp.valueOf(DateFormatUtil.format(new Date()))));
            add(eadmin.getUserPhone());
            add(status);
        }});
        }

        return update<=0?false:true;
    }
    //TODO 用户信息修改后，快件原本的信息也需要修改
    /**
     * 用户修改
     * @param eadmin 用户信息
     * @return 返回是否修改成功
     */
    @Override
    public boolean update(Eadmin eadmin) {
        int update=0;
        try {
            update = update(SQL_UPDATE, new ArrayList() {{
                add(eadmin.getName());
                add(eadmin.getUsername());
                add(eadmin.getPassword());
                add(eadmin.getUserPhone());
                add(eadmin.getIdNO());
                add(String.valueOf(eadmin.getId()));
            }});
        } catch (DuplicateNumberException e) {
            e.printStackTrace();
        } catch (DuplicateCodeException e) {
            e.printStackTrace();
        }
        return update <= 0 ?false:true;
    }

    //TODO 用户删除，必须其下面没有需要接收的快递才可以删除，删除时需要回显，其下面是否还有快递
    /**
     * 快递的删除用户删除
     * param id   用户的id
     *
     * @param id
     * @return 返回是否删除成功
     */
    @Override
    public boolean delete(int id,int status) {
        int update=0;
        try {
            update = update(SQL_DELETE, new ArrayList() {{
                add(id);
                add(status);
            }});
        } catch (DuplicateNumberException e) {
            e.printStackTrace();
        } catch (DuplicateCodeException e) {
            e.printStackTrace();
        }
        return update <= 0 ?false:true;
    }

    /**
     * 根据ID查询对应的用户信息
     *
     * @param id
     * @return
     */
    @Override
    public Eadmin findById(int id) {
        List list = selectQueryAll(select(SQL_FIND_BY_ID, new ArrayList() {{
            add(String.valueOf(id));
        }}), Eadmin.class, null);
        return (Eadmin) list.get(0);
    }
}
